import promisePool from './Pool.js'
import dayjs from 'dayjs'

export default class MinArticleDao {

    
    static async insert (min_article) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            //获取对象中属性值不为null的键,值
            let keys = []
            let vals = []
            for (const key of Reflect.ownKeys(min_article)) {
                if (min_article[key] !== null) {
                    keys.push(key)
                    vals.push(min_article[key])
                }
            }

            //遍历拼接sql
            let sql = 'insert into `min_article` ('
            let sql_fields = ''
            let sql_vals = ''

            for (let i = 0; i < keys.length; i++) {
                i == keys.length - 1 ? sql_fields += '??)' : sql_fields += '??,'
                i == keys.length - 1 ? sql_vals += '?)' : sql_vals += '?,'
            }

            sql += sql_fields
            sql += ' values ('
            sql += sql_vals
        
            let result = await connection.query(sql, [...keys, ...vals])

            return result[0].affectedRows
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async delete (min_article) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let result = await connection.query('delete from `min_article` where `id` = ?', min_article.id)

            return result[0].affectedRows
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async update (min_article) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            //获取对象中属性值不为null的键,值对
            let keys = []
            let key_val = []
            for (const key of Reflect.ownKeys(article)) {
                if (article[key] !== null) {
                    keys.push(key)
                    key_val.push(key)
                    key_val.push(article[key])
                }
            }

            //遍历拼接sql
            let sql = 'update `min_article` set '
            let sql_sets = ''

            for (let i = 0; i < keys.length; i++) {
                i == keys.length - 1 ? sql_sets += '?? = ?' : sql_sets += '?? = ?,'
            }

            sql += sql_sets
            sql += ' where id = ?'
        
            let result = await connection.query(sql, [...key_val, min_article.id])

            return result[0].affectedRows
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectById (id) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let [[ min_article ]] = await connection.query('select `id`, `name`, `content`, `files`, `likes`, `update_time`, `user_id` from `min_article` where `id` = ?', id)

            if (min_article.files) min_article.files = JSON.parse(min_article.files)
            return min_article
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectByLimit (start, end) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let [ min_articles ] = await connection.query('select `id`, `name`, `content`, `files`, `likes`, `update_time`, `user_id` from `min_article` limit ?, ' + end, start)

            let user_promise_arr = []
            let review_promise_arr = []
            for (const min_article of min_articles) {
                if (min_article.files) min_article.files = JSON.parse(min_article.files)

                min_article.update_time = dayjs(min_article.update_time).format('YYYY-MM-DD HH:mm:ss')

                user_promise_arr.push( connection.query('select `id`, `name` from `my_user` where `id` = ?', min_article.user_id) )

                review_promise_arr.push( connection.query('select count(id) as count from `review` where `article_id` = ?', min_article.id) )
            }

            let users = await Promise.all(user_promise_arr)
            let reviews = await Promise.all(review_promise_arr)

            for (let i = 0; i < min_articles.length; i++) {
                min_articles[i].user = users[i][0][0]
                min_articles[i].reviewCount = reviews[i][0][0].count
            }

            return min_articles
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectCount () {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let result = await connection.query('select count(id) as total from `min_articles`')

            return result[0][0]
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }
}